##########################################################################################################
## Author: Kjell R. Kjørstad                                                                            ##
## E-mail: kjell@kolug.org - for questions or comments.          					##
## File: This is the ircuac (IRC User and Access -Control) Module,					##
##	 the name is not suited anymore, but I'll keep it.			                        ##
## Purpose: This is the module which handles all database interactions in the BOT. 		        ##
##	    Adduser, Deluser, accesscheck, setpass and so on, pretty much everything which  		##
##	    is default in is code from this file.							##
##													##
##       												##
##           												##
## NOTE: If you do changes to existing code in any way, or have other comments an e-mail would be       ##
##	 highly appreciated.          	                        					##
## 													## 
## License:												##
##       This file is part of chanKeeper.								##
##													##
##       chanKeeper is free software: you can redistribute it and/or modify				##
##       it under the terms of the GNU General Public License as published by				##
##       the Free Software Foundation, either version 3 of the License, or				##
##       (at your option) any later version.								##
##													##
##       chanKeeper is distributed in the hope that it will be useful,					##
##       but WITHOUT ANY WARRANTY; without even the implied warranty of					##
##       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the					##
##       GNU General Public License for more details.							##
##   													##
##       You should have received a copy of the GNU General Public License				##
##       along with chanKeeper.  If not, see <http://www.gnu.org/licenses/>.				##
## 													##
## Copyright: Copyright © 2009, Kjell R. Kjørstad							##
##                                                                                                      ##
##########################################################################################################



package ircuac;

use DBI;
use warnings;
use strict;
require "conf/ircuac.conf";
our ($access_db_host, $access_db_name, $access_db_user, $access_db_pass, $minpasslength, $ident_timeout, $defaultuserpass);

#Subroutine for hashing whatever sent into it, routine will return the hash. - Used for passwords in module.
sub md5hash {
    my $md5 = Digest::MD5->new;
    $md5->add($_[0]);
    my $md5hash = $md5->hexdigest;
    return $md5hash;
}

#Subroutine which return username of logged in users when feeded with nickname.
sub get_uname {
    my ($nick) = @_;
    print("\n\n\n\n\n\nParametere: $nick\n\n\n\n");
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    my $checkifexist = ("SELECT username FROM users WHERE nick = '$nick'");
    my $docheck = $irc_db->prepare($checkifexist);
    $docheck->execute();
    my $check = $docheck->fetchrow();
    my @return;
    
    if ( $check ) {
	return($check);
    } else {
	push @return, ("No user with that nick logged in.");
	return(@return);
    }
}

#Subroutine which return userID of logged in users when feeded with nickname.
sub get_uid {
    my ($nick) = @_;
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    my $checkifexist = ("SELECT id FROM users WHERE nick = '$nick'");
    my $docheck = $irc_db->prepare($checkifexist);
    $docheck->execute();
    my $check = $docheck->fetchrow();
    my @return;
    print("\n\n\n\n\n\n\n\n\n$check\n\n\n\n\n\n\n\n\n");
    
    if ( $docheck->fetchrow() ) {
	return $check;
    } else {
	push @return, ("No user with that nick logged in.");
	return(@return);
    }
}

#Subroutine for adding users to user data base.
sub adduser {
    my ($class, $username, $access) = @_;
    #DBI Connection string.
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    #Statement to check if user exist.
    my $checkifexist = ("SELECT username FROM users WHERE username = '$username'");
    #Preparing statement.
    my $docheck = $irc_db->prepare($checkifexist);
    #Executes prepared statement.
    $docheck->execute();
    #Adding the fetchrow from cquery to $check.
    my $check = $docheck->fetchrow();
    #Hashing the temppass, makes it easier to handle later.    
    my $password = md5hash($defaultuserpass);
    #Adding new nick to $checkwith.
    my $checkwith = $username;
    my $time = time;
    

    if ( $check eq $checkwith ) {
	return 0;
    }

    if ( $check ne $checkwith ) {
	$irc_db->do("INSERT INTO users (username, access, password, useraddtstamp, approved, approved_status) VALUES ('$username', '$access', '$password', '$time', 'ADDED BY ADMIN', '2')");
	return 1;
    }
    $irc_db->disconnect();
}

#Subroutine for deleting users from user data base. 
sub deluser {
    my ($class, $username) = @_;
    #DBI Connection string.
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    #Statement to check if user exist.
    my $cquery = ("SELECT username FROM users WHERE username = '$username'");
    #Preparing statement.
    my $docheck = $irc_db->prepare($cquery);
    #Executes prepared statement.
    $docheck->execute();
    #Adding the fetchrow from cquery to $check.
    my $check = $docheck->fetchrow();
    #Adding user to delete to $checkwith.
    my $checkwith = $username;

    if ( $check eq $checkwith ) {
	$irc_db->do("DELETE FROM users WHERE username = '$username'");
	return 1;
    }

    if ( $check ne $checkwith ) {
	return 0;
    }
    
    #Send finish call to DBI.
    $docheck->finish();
    #Disconnect from MySQL.
    $irc_db->disconnect();
}

sub accesscheck {
    my ($class, $nickname, $hostname) = @_;
    #DBI Connection string.
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    my $time 	 = time;

    #Statements to get users nick, host and tstamp
    my $getrows	     = ("SELECT nick, host, useridenttstamp, access, approved_status, suspended, suspend_reason FROM users WHERE nick = '$nickname'");
    #Preparing statements.
    my $get_rows = $irc_db->prepare($getrows);
    #Executing $getrows.
    $get_rows->execute();
    #Adding the fetchrow from checkoldpass to $check.
    my @fetch_rows = $get_rows->fetchrow();
    my $timediff = $time-$fetch_rows[2];

    if ( $nickname eq $fetch_rows[0] and $hostname eq $fetch_rows[1] and $timediff < $ident_timeout ) {
	  
	if ( $fetch_rows[4] != '2' ) {
	    return 1;
	}
      
	if ( $fetch_rows[3] >= '0' and $fetch_rows[3] < '50' ) {
	    return 0;
	}

	if ( $fetch_rows[3] >= '50' and $fetch_rows[3] < '100' ) {
	    return 50;
	}

	if ( $fetch_rows[3] >= '100' and $fetch_rows[3] < '200' ) {
	    return 100;
	}

	if ( $fetch_rows[3] >= '200' and $fetch_rows[3] < '500' ) {
	    return 200;
	}
	
	if ( $fetch_rows[3] >= '500' and $fetch_rows[3] < '1000' ) {
	    return 500;
	}

 	if ( $fetch_rows[3] >= '1000' ) {
	    return 1000;
	}
    } else {
	return 90;
    }
}

#Identify will be made after accesscheck routine.
sub identify {
    #DBI Connection string.
    my ($class, $nickname, $username, $password, $hostname) = @_;
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    my $hashpass = md5hash($password);
    
    my $identts  = time;
    #Statement to get users password.
    my $checkpass = ("SELECT username, password, suspended, suspend_reason FROM users WHERE username = '$username'");
    #Preparing statement.
    my $prep = $irc_db->prepare($checkpass);
    #Executes $checkoldpass.
    $prep->execute();
    #Adding the fetchrow from checkoldpass to $check.
    my @check = $prep->fetchrow();
    my $sendbackcheck3 = $check[3];
    

    if ( $check[2] == '1' ) {
	return 3;
    }

    if ( $hashpass eq $check[1] ) {
	$irc_db->do("UPDATE users SET nick = '$nickname', host = '$hostname', useridenttstamp = '$identts' WHERE username = '$username'");
	my $ip = hostname($hostname);
	$irc_db->do("INSERT INTO identlog (username, nick, ip, timestamp) VALUES ('$username', '$nickname', '$ip', '$identts')");
	return 1;
    }

    if ( $username ne $check[0] ) {
	return 2;
    }    

    if ( $password ne $check[1] ) {
	return 0;
    }
}

sub unidentify {
    my ($class, $nick, $who) = @_;
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    #Statement to get users password.
    my $checkuser = ("SELECT nick, host FROM users WHERE nick = '$nick'");
    #Preparing statement.
    my $prep = $irc_db->prepare($checkuser);
    #Executes $checkuser.
    $prep->execute();
    #Adding the fetchrow from checkoldpass to $check.
    my @check = $prep->fetchrow();
    
    if ( $check[0] eq $nick and $check[1] eq $who ) {
	$irc_db->do("UPDATE users SET host = 'NULL' WHERE nick = '$nick'");
	return 1;
    } else {
	return 0;
    }
}

sub changepass {
    my ($class, $nickname, $oldpass, $newpass) = @_;
    #DBI Connection string.
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    #Statement to get users password.
    my $checkoldpass = ("SELECT password FROM users WHERE nick = '$nickname'");
    #Preparing statement.
    my $prep = $irc_db->prepare($checkoldpass);
    #Executes $checkoldpass.
    $prep->execute();
    #Adding the fetchrow from checkoldpass to $check.
    my $check = $prep->fetchrow();
    #Counts letters in password to ensure that it's long enough.
    my $count = $newpass =~ s/([0-9]|[a-z])/$1/gi;
    
    #Hashing new and old passwords.
    my $newhashpass = md5hash($newpass);
    my $oldhashpass = md5hash($oldpass);

    if ( $count < $minpasslength ) {
	return 3;
    }
    
    if ( $check ne $oldhashpass ) {
	return 0; 
    }
    
    if ( $check eq $oldhashpass ) {
	$irc_db->do("UPDATE users SET password = '$newhashpass' WHERE nick = '$nickname'");
	return 1;
    }
    
    #Send finish call to DBI.
    $prep->finish();
    #Disconnect from MySQL.
    $irc_db->disconnect();
}

sub register {
    my ($class, $user,$password,$nick) = @_;
    #DBI Connection string.
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    #Statement to check if user exist.
    my $checkifexist = ("SELECT username FROM users WHERE username = '$user'");
    #Preparing statement.
    my $docheck = $irc_db->prepare($checkifexist);
    #Executes prepared statement.
    $docheck->execute();
    #Adding the fetchrow from cquery to $check.
    my $check = $docheck->fetchrow();
    #Hashing the temppass, makes it easier to handle later.    
    my $hashedpassword = md5hash($password);
    #Adding new nick to $checkwith.
    my $checkwith = $user;
    my $time = time;
    my $count = $password =~ s/([0-9]|[a-z])/$1/gi;

    if ( $check eq $checkwith ) {
	return 0;
    }

    if ( $count < $minpasslength ) {
	return 2;
    }

    if ( $check ne $checkwith ) {
	$irc_db->do("INSERT INTO users (nick, username, access, password, useraddtstamp) VALUES ('$nick', '$user', 'NULL', '$hashedpassword', '$time')");
	return 1;
    } 
    $irc_db->disconnect();

}

sub listadmins {
    #DBI Connection string.
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    #Statement to check if user exist.
    my $askforadmin = ("SELECT nick, username, access FROM users WHERE access >= '500'");
    #Preparing statement.
    my $ask = $irc_db->prepare($askforadmin);
    #Executes prepared statement.
    $ask->execute();
    my @returntext;
    while ( my @admins = $ask->fetchrow() ) {
	push @returntext, ("Last nickname: $admins[0] - Username: $admins[1] - Access: $admins[2]\n");
    }
    return(@returntext);
}

sub list_approve {
    #DBI Connection string.
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    #Statement to check if user exist.
    my $askforapprovals = ("SELECT nick, username FROM users WHERE approved_status != '2'");
    #Preparing statement.
    my $ask = $irc_db->prepare($askforapprovals);
    #Executes prepared statement.
    $ask->execute();
    my @returntext;
    while ( my @approvals = $ask->fetchrow() ) {
	push @returntext, ("Nickname when register: $approvals[0] - Registered username: $approvals[1]\n");
    }
    return(@returntext);
}

#Approver: Fixed some problems with if's, they are sorted out. It will now return correctly.
sub approve {
    my ($class, $approver, $username, $access) = @_;
        
    #DBI Connection string.
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    #Statement to check Approvers access level. + statement to check if the user admin is trying to approve exists and is waiting for approval,
    my $checkaccess = ("SELECT access FROM users WHERE nick = '$approver'");
    #Preparing statements.
    my $ask = $irc_db->prepare($checkaccess);
    #Executes prepared statements.
    $ask->execute();
    #Adding $ask->fetchrow to $check.
    my $check = $ask->fetchrow();
    $ask->finish();
    #Second db contact.
    my $checktheuser  = ("SELECT username, approved, approved_status, suspended FROM users WHERE username = '$username'");
    my $checkuser = $irc_db->prepare($checktheuser);
    $checkuser->execute();
    my @usercheck = $checkuser->fetchrow();
    $checkuser->finish();

    if ( $usercheck[3] == '0' and $check > $access and $username eq $usercheck[0] and $usercheck[2] != '2' ) {
	my $ts = scalar localtime;
	$irc_db->do("UPDATE users SET approved = 'approved - [$ts] by $approver', approved_status = '2', access = $access WHERE username = '$username'"); 
	return 0;
    }

    if ( $username eq $usercheck[0] and $usercheck[3] == '1'  ) {
	return 2;
    }

    if ( $usercheck[2] == '2' and $username eq $usercheck[0] ) {
	return 3;
    }

    if ( $check < $access ) {
	return 1;
    }    
}

sub suspend {
    #DBI Connection string.
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    my $ts = scalar localtime;
    my $statusreq = $_[1];
    my $username = $_[2];
    my $hostname = $_[3];
    my $reason = "[$ts]$_[4]";
    my $user_access = suspend_levelcheck($username);
    #Suspend_levelcheck not used for the one who does !suspend command, because nick is sent for conveniance here, the user will be logged in anyways.
    my $statreq_access = accesscheck("0",$statusreq,$hostname);
    #Statements to get users nick, host and tstamp
    my $getrows	     = ("SELECT id, username, suspended FROM users WHERE username = '$username'");
    my $getrows_comcheck	     = ("SELECT id, username, suspended FROM users WHERE nick = '$username'");
    #Preparing statements.
    my $get_rows = $irc_db->prepare($getrows);
    my $get_rows_comcheck = $irc_db->prepare($getrows_comcheck);
    #Executing $getrows.
    $get_rows->execute();
    $get_rows_comcheck->execute();
    #Adding the fetchrow from checkoldpass to $check.
    my @suspended = $get_rows->fetchrow();
    my @suspended_comcheck = $get_rows_comcheck->fetchrow();
    my $suspendts  = time;

    if ( $suspended[2] == '1' ) {
	return 3;
    }

    if ( $user_access < $statreq_access and $statreq_access >= '500' ) {
	$irc_db->do("UPDATE users SET host = 'NULL', suspend_reason = '$reason', suspended = '1', suspended_by = '$statusreq' WHERE username = '$username'");
	$irc_db->do("INSERT INTO suspendlog (id, time, username, suspended_by, reason) VALUES ('$suspended[0]', '$suspendts', '$username', '$statusreq', '$reason')");
	return 1;
    }

    if ( $statusreq eq "SYSTEM" ) {
	$irc_db->do("UPDATE users SET host = 'NULL', suspend_reason = '$reason', suspended = '1', suspended_by = '$statusreq' WHERE username = '$suspended_comcheck[1]'");
	$irc_db->do("INSERT INTO suspendlog (id, time, username, suspended_by, reason) VALUES ('$suspended_comcheck[0]', '$suspendts', '$suspended_comcheck[1]', '$statusreq', '$reason')");
    }

    if ( $user_access < $statreq_access or $statreq_access < '500' ) {
	return 0;
    }
}

#Levelcheck only for suspend and pending, this will return access level when suspend_levelcheck($username) is called.
sub suspend_levelcheck {
    my $username = $_[0];
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    #Statements to get users nick, host and tstamp
    my $getrows	     = ("SELECT access FROM users WHERE username = '$username'");
    #Preparing statements.
    my $get_rows = $irc_db->prepare($getrows);
    #Executing $getrows.
    $get_rows->execute();
    #Adding the fetchrow from checkoldpass to $check.
    my $access = $get_rows->fetchrow();
    return $access;
}

sub unsuspend {
    my ($class, $statusreq, $suspended_username, $reason) = @_;
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    my $ts = scalar localtime;
    #Statements to get users nick, host and tstamp
    my $access_unsuspend     = ("SELECT access FROM users WHERE nick = '$statusreq'");
    my $suspended_by = ("SELECT access from users WHERE nick IN (SELECT suspended_by FROM users WHERE username = '$suspended_username')");
    #Preparing statements.
    my $get_rows = $irc_db->prepare($suspended_by);
    my $get_unsuspend = $irc_db->prepare($access_unsuspend);
    #Executing $getrows.
    $get_rows->execute();
    $get_unsuspend->execute();
    #Adding the fetchrow from checkoldpass to $check.
    my $access_suspendedby = $get_rows->fetchrow();
    my $access_unsuspende = $get_unsuspend->fetchrow();
    
    if ( $access_suspendedby eq '' ) {
	return 3;
    }

    if ( $access_suspendedby > $access_unsuspende ) {
	return 2;
    }
    
    if ( $access_suspendedby <= $access_unsuspende ) {
	$irc_db->do("UPDATE users SET suspend_reason = 'NULL', suspended = '0' WHERE username = '$suspended_username'");
	return 0;
    }
}

sub list_suspended {
    #DBI Connection string.
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    #Statement to check if user exist.
    my $askforsuspended = ("SELECT username, suspended_by, suspend_reason FROM users WHERE suspended = '1'");
    #Preparing statement.
    my $ask = $irc_db->prepare($askforsuspended);
    #Executes prepared statement.
    $ask->execute();
    my @returntext;
    while ( my @suspended = $ask->fetchrow() ) {
	push @returntext, ("Username: $suspended[0], suspended by $suspended[1] with reason: $suspended[2]\n");
    }
    return(@returntext);
}

sub hostname {
  $_[0] =~ s/^(.+)@//;
  my (@bytes, @octets,
    $packedaddr,
    $raw_addr,
    $host_name,
    $ip
  );

  if($_[0] =~ /[a-zA-Z]/g) {
    $raw_addr = (gethostbyname($_[0]))[4];
    @octets = unpack("C4", $raw_addr);
    $host_name = join(".", @octets);
  } else {
    $host_name = $_[0];
  }
  return($host_name);
}

sub leave_message {
    my ($class, $sender_nick, $sender_who, $reciever, @message) = @_;
    #The way this works now, the !leavemessage give <nick+message> we strip away the nick.
    splice(@message, 0, 1);
    #DBI Connection string.
    my $sender_connection = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    my $recievers_connection = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    #Statement to get userIDs.
    my $getSendersID = ("SELECT id FROM users WHERE nick = '$sender_nick'");
    my $getRecieversID = ("SELECT id FROM users WHERE username = '$reciever' OR nick = '$reciever'");
    #Preparing statement.
    my $getSID = $sender_connection->prepare($getSendersID);
    my $getRID = $recievers_connection->prepare($getRecieversID);
    #Executes prepared statement.
    $getSID->execute();
    $getRID->execute();
    #Getting username from the person who is logged in with the nick which is sent to bot when running message command to add to message.  
    
    my $sender_ID = $getSID->fetchrow();
    my $reciever_ID = $getRID->fetchrow();
    #Sending the "who" from IRC into hostname, hostname will strip everything but the host and just return the IP. 
    my $ip = hostname($sender_who);
    my $unixts = time;

    #Next lines get message count and increment the value.
    my $count_connect = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    my $getMsgCount = ("SELECT COUNT(*) FROM messagetbl WHERE to_id = '$reciever_ID'");
    my $getCount = $count_connect->prepare($getMsgCount);
    $getCount->execute();
    my $user_msg_ID = $getCount->fetchrow();
    my $user_message_ID = $user_msg_ID++;


    print("\n\n\n\nThis is the incremented message value: $user_message_ID - $user_msg_ID\n\n\nThis is send to: $reciever with ID: $reciever_ID\n\nThis is Sent from: $sender_who \@ $ip with ID: $sender_ID\n\nThis is message: @message\n\n\n\n");
    if ( accesscheck($sender_nick) >= '0' and $reciever_ID != '0' ) {
	$irc_db->do("INSERT INTO messagetbl (user_message_id, message_time, to_id, from_id, from_ip, message_text) VALUES ('$user_msg_ID', '$unixts', '$reciever_ID', '$sender_ID', '$ip', '@message')");
	return 1;
    } else {
	return 2;
    }
}

sub checkmessages {
    my ($class, $nick) = @_;
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    #my $getMessages = ("SELECT user_message_id, from_id,from_unixtime(message_time) FROM messagetbl WHERE read = 'NULL' AND to_id = IN (SELECT id FROM users WHERE nick = '$nick')");
    my $getMessages = ("SELECT m.user_message_id, u2.nick, from_unixtime(m.message_time) 
			FROM messagetbl m
			JOIN users u2 ON (u2.id=m.from_id)
			JOIN users u ON (u.id=m.to_id)
			WHERE u.nick = '$nick'
			ORDER BY m.user_message_id");
    my $getmsg = $irc_db->prepare($getMessages);
    $getmsg->execute();
    my @messages;
    my $count = message_count("0",$nick);

    while ( my @msgs = $getmsg->fetchrow() ) {
	    push @messages, ("$msgs[0] sent from $msgs[1] \@ $msgs[2]\n"); 
    }
    return(@messages);
  
}

sub readmessage {
    my ($class, $nick, $message_id) = @_;
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    my $getMessage = ("SELECT m.message_text, u.nick, from_unixtime(m.message_time) 
		       FROM messagetbl m
		       JOIN users u ON (u.id=m.to_id)
		       WHERE u.nick = '$nick' 
		       AND 
		       m.user_message_ID = '$message_id'");
    my $getmsg = $irc_db->prepare($getMessage);
    $getmsg->execute();
    my @message;
    
    while ( my @msg = $getmsg->fetchrow() ) {
	push @message, ("From $msg[1] \@ $msg[2]; $msg[0]\n");
    }
    $irc_db->do("UPDATE messagetbl SET is_read = 'YES'  WHERE user_message_id = '$message_id' and to_id IN (SELECT id FROM users WHERE nick = '$nick')");
    return(@message);
}

sub message_count {
    my ($class, $nick) = @_;
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    my $all = ("SELECT COUNT(*) FROM messagetbl WHERE to_id IN (SELECT id FROM users WHERE nick = '$nick')");
    my $getall = $irc_db->prepare($all);
    $getall->execute();
    my $theAllcount = $getall->fetchrow();
    return($theAllcount);
}

sub new_message_count {
    my ($class, $nick) = @_;
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    my $new = ("SELECT COUNT(*) FROM messagetbl WHERE to_id IN (SELECT id FROM users WHERE nick = '$nick' AND is_read IS NULL)");
    my $getnew = $irc_db->prepare($new);
    $getnew->execute();
    my $theNewcount = $getnew->fetchrow();
    return($theNewcount);
}

sub delete_message {
    my ($class, $nick, $message) = @_;
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    my $checkexistence = ("SELECT COUNT(*) FROM messagetbl WHERE user_message_id = '$message' AND to_id IN (SELECT id FROM users WHERE nick = '$nick')");
    my $get = $irc_db->prepare($checkexistence);
    $get->execute;
    my $existence = $get->fetchrow();


    if ( $message =~ /^ALL$/i ) {
	$irc_db->do("DELETE FROM messagetbl WHERE to_id IN (SELECT id FROM users WHERE nick = '$nick')");
	return 0;
    } 

    if ( $message =~ /^\d$/ and $existence == '1' ) {
	$irc_db->do("DELETE FROM messagetbl WHERE to_id IN (SELECT id FROM users WHERE nick = '$nick' AND user_message_id = '$message')");
	return 1;
    } 
    
    if ( $message =~ /^\d$/ and $existence == '0' ) {
	return 3;
    }
}

#A subroutine which logs executed commands to logtbls in data base, added commandstatus so that this
#routine can do both authorized(successful) commands and unauthorized(FAILED) commands.
#This is to enable the possibility to do logging of attempts to do stuff which the user can't.
sub commandlog {
    my ($class, $command, $who, $nick, $complete_command, $commandstatus) = @_;
    my $irc_db = DBI->connect("DBI:mysql:$access_db_name:$access_db_host","$access_db_user","$access_db_pass");
    my $unixts = time;
    my $userid = get_uid($nick);
    my $username = get_uname($nick);
    
    if ( $commandstatus == '0' ) {
	$irc_db->do("INSERT INTO failed_commandlogtbl (time, command, user, nick, who, complete_command) VALUES ('$unixts', '$command', '$username', '$nick', '$who', '$complete_command')");
    }
    
    if ( $commandstatus == '1' ) {
	$irc_db->do("INSERT INTO commandlogtbl (time, command, user, nick, who, complete_command) VALUES ('$unixts', '$command', '$username', '$nick', '$who', '$complete_command')");
    }
}

1;
